package com.itheima.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.time.LocalDate;
import java.util.List;
import java.util.Map;

@Mapper
public interface MemberMapper {

    //根据月份查询会员数量
    @Select("SELECT \n" +
            "  DATE_FORMAT(regTime, '%Y-%m') AS months,\n" +
            "  COUNT(*) AS memberCount\n" +
            "FROM\n" +
            "  t_member \n" +
            "GROUP BY DATE_FORMAT(regTime, '%Y-%m') \n" +
            "ORDER BY months ")
    List<Map<String, Object>> findMemberCountByMonth();


    /*
     * 根据日期查询新增会员数量
     */
    @Select("SELECT COUNT(*) FROM t_member WHERE regTime = #{reportDate}")
    Integer todayNewMember(LocalDate reportDate);

    //本周新增会员数量
    @Select("SELECT COUNT(*) FROM t_member WHERE regTime BETWEEN #{startDate} AND #{endDate}")
    Integer thisWeekNewMember(LocalDate startDate,LocalDate endDate);

    //本月新增会员数量
    @Select("SELECT COUNT(*) FROM t_member WHERE regTime BETWEEN #{startDate} AND #{endDate}")
    Integer thisMonthNewMember(LocalDate startDate,LocalDate endDate);

    @Select("SELECT COUNT(*) FROM t_member")
    Integer totalMember();
}
